"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : dolphin
Case Name   : guc参数string_hash_compatible=off使用hashjoin方式执行连接查询
Create At   : 2022.04.06
Owner       : z@wan005
Description :
    1、创建兼容B数据库并查询插件
    2、设置guc参数string_hash_compatible=off并重启数据库
    3、构建数据执行hashjoin操作
    4、恢复环境
Expect      :
    1、成功
    2、设置成功，参数生效
    3、执行成功
    4、恢复清理成功
History     :
"""

import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest01(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.user_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_string_case54'

    def test_string(self):
        text = '-----step1:创建兼容B数据库并查询插件;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility ='B' " \
                  f"encoding = 'SQL_ASCII';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)
        sql_cmd = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('dolphin', sql_res, '插件加载失败' + text)

        text = '-----step2:设置guc参数string_hash_compatible=off并重启数据库;' \
               'expect:成功-----'
        self.log.info(text)
        guc_res = self.sh_primary.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            'string_hash_compatible=off', get_detail=True)
        self.assertIn(self.constant.GSGUC_SUCCESS_MSG, guc_res)

        restart_res = self.sh_primary.restart_db_cluster(get_detail=True)
        self.log.info(restart_res)
        self.assertIn(self.constant.START_SUCCESS_MSG, restart_res, '数据库重启失败')

        sql_cmd = "show string_hash_compatible;"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertIn('off', sql_res, '参数设置失败')

        text = '-----step3:以hashjoin方式执行连接查询;expect:成功-----'
        self.log.info(text)
        sql = f'set enable_mergejoin to off;' \
              f'set enable_nestloop to off;' \
              f'set enable_hashjoin to on;' \
              f'show enable_mergejoin;' \
              f'show enable_nestloop;' \
              f'show enable_hashjoin;'
        self.log.info(sql)
        sql_res = self.sh_primary.execut_db_sql(sql,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        check_lis = ['enable_mergejoin \n------------------\n off',
                     'enable_nestloop \n-----------------\n off',
                     'enable_hashjoin \n-----------------\n on']
        for msg in check_lis:
            self.assertIn(msg, sql_res, '参数设置失败')

        sql = "drop table if exists t_string_00054 cascade;" \
              "drop table if exists t_string_00054_1 cascade;" \
              "create table t_string_00054(c_1  varchar2(10));" \
              "create table t_string_00054_1(c_1  varchar2(10));" \
              "insert into t_string_00054 " \
              "values('1aA干'),('1aA干 '),('1aA干   ');" \
              "insert into t_string_00054_1 " \
              "values('1aA干'),('1aA干 '),('1aA干   ');" \
              "select * from t_string_00054 inner join t_string_00054_1 " \
              "on t_string_00054.c_1=t_string_00054_1.c_1;"
        self.log.info(sql)
        sql_res = self.sh_primary.execut_db_sql(sql,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        msg_lis = ['1aA干    | 1aA干\n', '1aA干    | 1aA干 \n',
                   '1aA干    | 1aA干   \n', '(3 rows)']
        for msg in msg_lis:
            self.assertIn(msg, sql_res, '执行失败')

    def tearDown(self):
        self.log.info('-----step4:环境清理成功;expect:成功-----')
        res = self.sh_primary.execute_gsguc(
              'set', self.constant.GSGUC_SUCCESS_MSG,
              'string_hash_compatible=on', get_detail=True)
        restart_res = self.sh_primary.restart_db_cluster(get_detail=True)
        self.log.info(restart_res)
        sql_cmd = "show string_hash_compatible;"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        sql = f"drop database if exists {self.db_name};"
        self.log.info(sql)
        drop_res = self.sh_primary.execut_db_sql(sql)
        self.assertIn(self.constant.GSGUC_SUCCESS_MSG, res, 'guc参数设置失败')
        self.assertIn(self.constant.START_SUCCESS_MSG, restart_res, '数据库重启失败')
        self.assertIn('on', sql_res, '参数设置失败')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res, '删除数据库失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
